﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;


namespace CCPSIP
{
    public class iBeaconKit
    {
        /// <summary>取得Oracle連線字串(ISENDER_ORACLE)</summary>
        private static string GetConnStrIsenderOracle()
        {
            //iSender Oracle
            return "SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.52.195)(PORT=1521)))(CONNECT_DATA =(SERVER=DEDICATED)(SID=UNIDB)));uid=ISENDER;pwd=aKhJJFA2sWfEsQz0Zm5iwQi9gjiVND";

            //ERP
            //return "SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=203.69.132.121)(PORT=1521)))(CONNECT_DATA =(SERVER=DEDICATED)(SERVICE_NAME = erp)));uid=SIP;pwd=YMNUTKWQ9PFTKUGBNQU2";
        }

        /// <summary>驗證iBeacon是否存在</summary>
        /// <returns>iBeacon是否存在(True, False)</returns>
        public static bool IsBeaconExist(string Major, string Minor)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrIsenderOracle());

            string selStrOra = "SELECT UUID FROM BEACON"
              + " WHERE MAJOR = :MAJOR"
              + " AND MINOR = :MINOR"
              + " AND IS_ACTIVATE = 'TRUE'";

            OracleCommand selCmdOra = new OracleCommand(selStrOra, connOracle);
            selCmdOra.Parameters.Add("MAJOR", OracleType.VarChar).Value = Major;
            selCmdOra.Parameters.Add("MINOR", OracleType.VarChar).Value = Minor;

            connOracle.Open();
            string Beacon_UUID = Convert.ToString(selCmdOra.ExecuteScalar());
            connOracle.Close();

            if (string.IsNullOrEmpty(Beacon_UUID))
                return false;
            else
                return true;
        }

        /// <summary>取得指定iBeacon的Rule</summary>
        /// <returns>符合iBeacon位置的Rule List</returns>
        public static List<Rule> GetRuleList(string Major, string Minor, string Proximity)
        {
            List<Rule> RuleList = new List<Rule>();

            try
            {
                DateTime reqTime = DateTime.Now;

                OracleConnection connOracle = new OracleConnection(GetConnStrIsenderOracle());

                //串接SQL查詢指令
                string selStrOra = "SELECT RULE.ID, RULE_NAME, RULE_CNAME, RULE_DESCRIPTION, ACTION_TYPE, ACTION_COMMAND, REMIND_TYPE, BEGIN_TIME, END_TIME, REGION_NAME, MAP_NAME, MAP_X_AXIS, MAP_Y_AXIS, FORCE_BACKGROUND"
                                + " FROM RULE, RULE_AND_REGION, REGION, REGION_AND_BEACON, BEACON, PROXIMITY"
                                + " WHERE RULE.ID = RULE_AND_REGION.RULE_ID"
                                + " AND RULE_AND_REGION.REGION_ID = REGION.ID"
                                + " AND REGION.ID = REGION_AND_BEACON.REGION_ID"
                                + " AND REGION_AND_BEACON.BEACON_ID = BEACON.ID"
                                + " AND RULE_STATUS = 'Y'"                            //【Check 01】檢查RULE_STATUS = 'Y'
                                + " AND RULE.IS_ACTIVATE = 'TRUE'"                    //【Check 02-1】檢查IS_ACTIVATE = 'TRUE'
                                + " AND REGION.IS_ACTIVATE = 'TRUE'"                  //【Check 02-2】檢查IS_ACTIVATE = 'TRUE'
                                + " AND trunc(:EFFECTIVE_TIME) > trunc(EFFECTIVE_TIME)"       //【Check 03】檢查EFFECTIVE_TIME
                                + " AND trunc(:EXPIRATION_TIME) < trunc(EXPIRATION_TIME)"      //【Check 04】檢查EXPIRATION_TIME
                                + " AND MAJOR = :MAJOR"
                                + " AND MINOR = :MINOR"
                                + " AND RULE.PROXIMITY = PROXIMITY.PROXIMITY"
                                + " AND PROXIMITY.VALUE >= ("                         //【Check 05】檢查PROXIMITY
                                    + " SELECT VALUE"
                                    + " FROM PROXIMITY"
                                    + " WHERE PROXIMITY = :PROXIMITY)";

                OracleCommand selCmdOra = new OracleCommand(selStrOra, connOracle);
                selCmdOra.Parameters.Add("EFFECTIVE_TIME", OracleType.DateTime).Value = reqTime;
                selCmdOra.Parameters.Add("EXPIRATION_TIME", OracleType.DateTime).Value = reqTime;
                selCmdOra.Parameters.Add("MAJOR", OracleType.VarChar).Value = Major;
                selCmdOra.Parameters.Add("MINOR", OracleType.VarChar).Value = Minor;
                selCmdOra.Parameters.Add("PROXIMITY", OracleType.VarChar).Value = Proximity;

                //執行SQL查詢指令
                connOracle.Open();
                OracleDataAdapter adapter = new OracleDataAdapter(selCmdOra);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                connOracle.Close();

                //將SQL查詢結果存入RuleList中
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    DateTime BeginTime, EndTime;
                    bool s = DateTime.TryParse(row["BEGIN_TIME"].ToString(), out BeginTime);
                    s = DateTime.TryParse(row["END_TIME"].ToString(), out EndTime);

                    if (s)   //兩個時間都轉型成功
                    {
                        if (DateTime.Now > BeginTime & DateTime.Now < EndTime)  //【Check 06】檢查BEGIN_TIME和END_TIME
                            RuleList.Add(new Rule(row));
                    }
                }
            }
            catch (Exception)
            {
            }

            return RuleList;
        }

        /// <summary>取得符合權限的Rule</summary>
        /// <returns>符合使用者權限的Rule List</returns>
        public static List<Rule> GetRuleList(SipUserInfo userInfo)
        {
            List<Rule> ReturnRuleList = new List<Rule>();
            OracleConnection connOracle = new OracleConnection(GetConnStrIsenderOracle());
            DataSet dataSet = new DataSet();

            //串接SQL查詢指令
            string selStrOra = "SELECT RULE.ID, RULE_NAME, RULE_CNAME, RULE_DESCRIPTION, ACTION_TYPE, ACTION_COMMAND, REMIND_TYPE, PROXIMITY, BEGIN_TIME, END_TIME, EFFECTIVE_TIME, EXPIRATION_TIME, FORCE_BACKGROUND, RULE_STATUS, RULE.IS_ACTIVATE, REGION_NAME, MAP_NAME, MAP_X_AXIS, MAP_Y_AXIS, REGION.IS_ACTIVATE"
                            + " FROM RULE, RULE_AND_REGION, REGION"
                            + " WHERE RULE.ID IN ("
                                + " SELECT DISTINCT RULE_AND_RULE_RIGHT.RULE_ID"
                                + " FROM RULE_AND_RULE_RIGHT"
                                + " WHERE RULE_RIGHT_ID IN ("
                                    + " SELECT RULE_RIGHT.ID"
                                    + " FROM RULE_RIGHT"
                                    + " WHERE USER_TYPE = 'PPS_CODE' AND USER_ID = :PPS_CODE"
                                    + " UNION"
                                    + " SELECT RULE_RIGHT.ID"
                                    + " FROM RULE_RIGHT"
                                    + " WHERE USER_TYPE = 'PHONE' AND USER_ID = :PHONE"
                                    + " UNION"
                                    + " SELECT RULE_RIGHT.ID"
                                    + " FROM RULE_RIGHT"
                                    + " WHERE USER_TYPE = 'DEPT_LIKE' AND :DEPT_NO LIKE USER_ID||'%'))"
                            + " AND RULE.ID NOT IN ("
                                + " SELECT RULE_AND_RULE_RIGHT.RULE_ID"
                                + " FROM RULE_AND_RULE_RIGHT"
                                + " WHERE RULE_RIGHT_ID IN ("
                                    + " SELECT RULE_RIGHT.ID"
                                    + " FROM RULE_RIGHT"
                                    + " WHERE USER_TYPE = 'BLOCK' AND USER_ID = :PPS_CODE))"
                            + " AND RULE.ID = RULE_AND_REGION.RULE_ID"
                            + " AND RULE_AND_REGION.REGION_ID = REGION.ID"
                            + " AND RULE.IS_ACTIVATE = 'TRUE'"
                            + " AND REGION.IS_ACTIVATE = 'TRUE'";

            OracleCommand selCmdOra = new OracleCommand(selStrOra, connOracle);
            selCmdOra.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = userInfo.PPS_CODE;
            string FullPhone = string.Format("{0}{1}", userInfo.CountryCode, userInfo.MobileNum);
            selCmdOra.Parameters.Add("PHONE", OracleType.VarChar).Value = FullPhone;
            selCmdOra.Parameters.Add("DEPT_NO", OracleType.VarChar).Value = userInfo.DEPT_NO;

            //執行SQL查詢指令
            connOracle.Open();
            OracleDataAdapter adapter = new OracleDataAdapter(selCmdOra);
            adapter.Fill(dataSet);
            connOracle.Close();

            //將SQL查詢結果存入ReturnRuleList中
            foreach (DataRow row in dataSet.Tables[0].Rows)
                ReturnRuleList.Add(new Rule(row));

            return ReturnRuleList;
        }

        /// <summary>檢查Rule是否符合重複規則</summary>
        /// <returns>符合重複規則的的Rule List</returns>
        public static List<Rule> CheckRepeatSchedule(List<Rule> Rules)
        {
            List<Rule> ReturnRuleList = new List<Rule>();

            try
            {
                DateTime reqTime = DateTime.Now;
                OracleConnection connOracle = new OracleConnection(GetConnStrIsenderOracle());

                foreach (Rule r in Rules)
                {
                    //串接SQL查詢指令
                    string selStrOra = "SELECT DISTINCT RULE_ID"
                                    + " FROM RULE_AND_REPEAT_SCHEDULE"
                                    + " WHERE REPEAT_SCHEDULE_ID IN("
                                        + " SELECT REPEAT_SCHEDULE.ID"
                                        + " FROM REPEAT_SCHEDULE"
                                        + " WHERE REPEAT_TYPE = 'DAILY'"
                                        + " UNION"
                                        + " SELECT REPEAT_SCHEDULE.ID"
                                        + " FROM REPEAT_SCHEDULE"
                                        + " WHERE REPEAT_TYPE = 'WEEKLY'"
                                        + " AND to_char(:DATETIME, 'D') = to_number(REPEAT_DAY)"
                                        + " UNION"
                                        + " SELECT REPEAT_SCHEDULE.ID"
                                        + " FROM REPEAT_SCHEDULE"
                                        + " WHERE REPEAT_TYPE = 'MONTHLY'"
                                        + " AND to_number(to_char(:DATETIME2, 'dd')) = to_number(REPEAT_DAY))";

                    OracleCommand selCmdOra = new OracleCommand(selStrOra, connOracle);
                    //selCmdOra.Parameters.Add("RULE_ID", OracleType.Number).Value = r.RuleId;
                    selCmdOra.Parameters.Add("DATETIME", OracleType.DateTime).Value = reqTime;
                    selCmdOra.Parameters.Add("DATETIME2", OracleType.DateTime).Value = reqTime;

                    //執行SQL查詢指令
                    connOracle.Open();
                    OracleDataAdapter adapter = new OracleDataAdapter(selCmdOra);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    connOracle.Close();

                    //將SQL查詢結果存入ReturnRuleList中
                    foreach (DataRow row in dataSet.Tables[0].Rows)
                    {
                        if (r.RuleId == Convert.ToInt32(row["RULE_ID"]))
                        {
                            ReturnRuleList.Add(r);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ChrisKit.SkipVar(ex);
            }

            return ReturnRuleList;
        }

        /// <summary>檢查Map是否存在資料庫中</summary>
        /// <returns>Map是否存在(Boolean)</returns>
        public static bool IsMapExist(string MapName)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrIsenderOracle());

            string selStrOra = "SELECT DISTINCT MAP_NAME"
                             + " FROM REGION"
                             + " WHERE MAP_NAME = :MAP_NAME";

            OracleCommand selCmdOra = new OracleCommand(selStrOra, connOracle);
            selCmdOra.Parameters.Add("MAP_NAME", OracleType.VarChar).Value = MapName;

            connOracle.Open();
            string map_name = Convert.ToString(selCmdOra.ExecuteScalar());
            connOracle.Close();

            if (string.IsNullOrEmpty(map_name))
                return false;
            else
                return true;
        }

        /// <summary>取得指定iBeacon的剩餘電量</summary>
        /// <returns>電量值</returns>
        public static int GetBatteryLife(string Major, string Minor)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrIsenderOracle());

            string selStrOra = "SELECT BATTERY_LIFE"
                             + " FROM BEACON"
                             + " WHERE MAJOR = :MAJOR"
                             + " AND MINOR = :MINOR";

            OracleCommand selCmdOra = new OracleCommand(selStrOra, connOracle);
            selCmdOra.Parameters.Add("MAJOR", OracleType.VarChar).Value = Major;
            selCmdOra.Parameters.Add("MINOR", OracleType.VarChar).Value = Minor;

            connOracle.Open();
            int battery_life = Convert.ToInt32(selCmdOra.ExecuteScalar());
            connOracle.Close();

            return battery_life;
        }

        /// <summary>更新指定iBeacon的剩餘電量</summary>
        /// <returns>無回傳值</returns>
        public static void UpdateBatteryLife(string Major, string Minor, int NewBatteryLife)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrIsenderOracle());

            string selStrOra = "UPDATE BEACON"
                             + " SET BATTERY_LIFE = :BATTERY_LIFE"
                             + " WHERE MAJOR = :MAJOR"
                             + " AND MINOR = :MINOR";

            OracleCommand selCmdOra = new OracleCommand(selStrOra, connOracle);
            selCmdOra.Parameters.Add("BATTERY_LIFE", OracleType.Number).Value = NewBatteryLife;
            selCmdOra.Parameters.Add("MAJOR", OracleType.VarChar).Value = Major;
            selCmdOra.Parameters.Add("MINOR", OracleType.VarChar).Value = Minor;

            connOracle.Open();
            selCmdOra.ExecuteNonQuery();
            connOracle.Close();
        }
    }

    /// <summary>Rule資訊</summary>
    //"RULE_NAME" : "WC_MALE",
    //"RULE_CNAME" : "台北資訊中心5樓男廁門禁",
    //"RULE_DESCRIPTION" : “請問您是否要開啟廁所門?”,
    //"ACTION_TYPE" : "QRCODE",
    //"ACTION_COMMAND" : "SERVICE_ID=POWER&Action=MaleZ9RXjW8K7UcarvWa",
    //"REMIND_TYPE" : "ALWAYS",
    //"REGION_NAME" : "資訊中心5樓男廁",
    //"MAP_NAME": "資訊中心五樓_2014-09-23-18-00".jpg",
    //"MAP_X_AXIS" : "10",
    //"MAP_Y_AXIS" : "10",
    //"FORCE_BACKGROUND": "FALSE"
    public class Rule
    {
        public int RuleId { get; set; }
        public string RuleName { get; set; }
        public string RuleCname { get; set; }
        public string RuleDescription { get; set; }
        public string ActionType { get; set; }
        public string ActionCommand { get; set; }
        public string RemindType { get; set; }
        public string RegionName { get; set; }
        public string MapName { get; set; }
        public int MapXAxis { get; set; }
        public int MapYAxis { get; set; }
        public bool ForceBackground { get; set; }

        //建構子
        public Rule(DataRow row)
        {
            RuleId = Convert.ToInt32(row["ID"]);
            RuleName = row["RULE_NAME"].ToString();
            RuleCname = row["RULE_CNAME"].ToString();
            RuleDescription = row["RULE_DESCRIPTION"].ToString();
            ActionType = row["ACTION_TYPE"].ToString();
            ActionCommand = row["ACTION_COMMAND"].ToString();
            RemindType = row["REMIND_TYPE"].ToString();
            RegionName = row["REGION_NAME"].ToString();
            MapName = row["MAP_NAME"].ToString();
            MapXAxis = Convert.ToInt32(row["MAP_X_AXIS"]);
            MapYAxis = Convert.ToInt32(row["MAP_Y_AXIS"]);
            ForceBackground = Convert.ToBoolean(row["FORCE_BACKGROUND"]);
        }

        //實做IEquatable<T>
        public bool Equals(Rule other)
        {
            if (other == null)
                return false;

            if (this.RuleId == other.RuleId)
                return true;
            else
                return false;
        }
        public override bool Equals(Object obj)
        {
            if (obj == null)
                return false;

            Rule RuleObj = obj as Rule;
            if (RuleObj == null)
                return false;
            else
                return Equals(RuleObj);
        }
        public override int GetHashCode()
        {
            return this.RuleId.GetHashCode();
        }
    }

    public class ReturnStatus
    {
        public string Major { get; set; }
        public string Minor { get; set; }
        public int OldBatteryLife { get; set; }
        public int NewBatteryLife { get; set; }
        public bool IsUpdate { get; set; }

        public ReturnStatus(string major, string minor, int old_battery_life, int new_battery_life, bool is_update)
        {
            Major = major;
            Minor = minor;
            OldBatteryLife = old_battery_life;
            NewBatteryLife = new_battery_life;
            IsUpdate = is_update;
        }
    }
}